<?php

namespace App\Console\Commands;

use App\Model\PearlConsumeStatistics;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;


class GeneratePearlConsumeStatisticsCommand extends Command
{
    private $prev = [];
    private $model = null;
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'generate:pearl_consume_statistics {type=0}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct(PearlConsumeStatistics $pearlConsumeStatistics)
    {
        parent::__construct();
        $this->model = $pearlConsumeStatistics;
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $type = (int) $this->argument('type');
        if($type === 0){
            $this->generateYesterdayData();
        } elseif ($type === 1) {
            $this->generateAll();
        }
    }

    private function generateYesterdayData(){
        $sql = "SELECT
	f_d.created_at created_date,
	f_d.gift_pearl,
	f_d.smash_eggs_pearl,
	f_d.activity_pearl,
	f_d.system_pearl,
	o_m_g.headwear_pearl,
	o_m_g.car_pearl,
	o_m_g.bubble_pearl,
	(
		f_d.gift_pearl + f_d.system_pearl + f_d.activity_pearl + f_d.smash_eggs_pearl + o_m_g.headwear_pearl + o_m_g.car_pearl + o_m_g.bubble_pearl
	) today_pearl
FROM
	(
		SELECT
			DATE_SUB(CURDATE(), INTERVAL 1 DAY) created_at,
			COALESCE(SUM(IF(pearl_type=0,ABS(pearl_value),0)),0) gift_pearl,
			COALESCE(SUM(IF(pearl_type=6,ABS(pearl_value),0)),0) smash_eggs_pearl,
			COALESCE(SUM(IF(pearl_type=7,ABS(pearl_value),0)),0) activity_pearl,
			COALESCE(SUM(IF(pearl_type=10,pearl_value,0)),0) system_pearl
		FROM
			zb_finance_pearl_stream
		WHERE
			pearl_type IN(0,6,7,8,10)
		AND change_timestamp BETWEEN UNIX_TIMESTAMP(
			DATE_SUB(CURDATE(), INTERVAL 1 DAY)
		)
		AND UNIX_TIMESTAMP(CURDATE())
	) f_d
LEFT JOIN (
	SELECT
		DATE_SUB(CURDATE(), INTERVAL 1 DAY) created_at,
		COALESCE(SUM(

			IF (
				m_g.goods_type = 1,
				m_s.price,
				0
			)
		), 0) headwear_pearl,
		COALESCE(SUM(

			IF (
				m_g.goods_type = 2,
				m_s.price,
				0
			)
		), 0) car_pearl,
		COALESCE(SUM(

			IF (
				m_g.goods_type = 3,
				m_s.price,
				0
			)
		), 0) bubble_pearl
	FROM
		zb_mall_shoplog m_s
	LEFT JOIN zb_mall_goods m_g ON m_s.goods_id = m_g.goods_id
	WHERE
		m_s.create_timestamp BETWEEN UNIX_TIMESTAMP(
			DATE_SUB(CURDATE(), INTERVAL 1 DAY)
		)
	AND UNIX_TIMESTAMP(CURDATE())
) o_m_g ON o_m_g.created_at = f_d.created_at";
        $yesterday_data = DB::connection('api')->selectOne($sql);
        //获取昨日累计值
        $before_yesterday_grand_data = DB::connection('mysql')->selectOne("SELECT today_pearl,grand_pearl FROM cms_pearl_consume_statistics ORDER BY id DESC LIMIT 1");

        // 累计产出 = 昨日累计产出 + 今日产出
        $grand_pearl = $before_yesterday_grand_data->grand_pearl + $yesterday_data->today_pearl;

        //消耗增长率
        if($before_yesterday_grand_data->today_pearl > 0){
            $growth_rate = $yesterday_data->today_pearl / $before_yesterday_grand_data->today_pearl - 1;
        } else {
            $growth_rate = 0;
        }

        $insert_data = collect($yesterday_data)->merge([
            'grand_pearl' => $grand_pearl,
            'growth_rate' => $growth_rate
        ])->toArray();

        if($this->model->query()->firstOrCreate($insert_data)){
            $this->info('ok');
        } else {
            $this->info('fail');
        }
    }

    /**
     * 统计昨日之前
     * @return array
     */
    private function generateAll(){
        // 最小日期
        $min_date_res = DB::connection('api')->selectOne("SELECT DATE_SUB(MIN(FROM_UNIXTIME(change_timestamp,'%Y-%m-%d')), INTERVAL 1 DAY) AS created_at FROM zb_finance_pearl_stream WHERE change_timestamp>0");
        if(!$min_date_res) return [];
        $sql = "SELECT
	t.created_at created_date,
	COALESCE(g_d.gift_consume, 0) gift_pearl,
	COALESCE(g_d.smash_eggs_consume, 0) smash_eggs_pearl,
	COALESCE(g_d.activity_consume, 0) activity_pearl,
	COALESCE(g_d.system_consume, 0) system_pearl,
	COALESCE(o_m_g.headwear_pearl,0) headwear_pearl,
	COALESCE(o_m_g.car_pearl,0) car_pearl,
	COALESCE(o_m_g.bubble_pearl, 0) bubble_pearl,
	(COALESCE(g_d.gift_consume,0) + COALESCE(g_d.activity_consume,0) + COALESCE(g_d.pearl_consume,0) + COALESCE(g_d.smash_eggs_consume,0) + COALESCE(g_d.system_consume,0) + COALESCE(o_m_g.headwear_pearl,0) + COALESCE(o_m_g.car_pearl,0) + COALESCE(o_m_g.bubble_pearl,0)) today_pearl
 FROM (SELECT
	@num :=@num + 1,
	DATE_FORMAT(
		adddate(
			'{$min_date_res->created_at}',
			INTERVAL @num DAY
		),
		'%Y-%m-%d'
	) AS created_at
FROM
	zb_finance_pearl_stream,
	(SELECT @num := 0) t
WHERE
	ADDDATE(
		'{$min_date_res->created_at}',
		INTERVAL @num DAY
	) < DATE_SUB(CURDATE(), INTERVAL 2 DAY)
ORDER BY
	created_at ASC
) t LEFT JOIN (
	SELECT
		SUM(IF(pearl_type=0,ABS(pearl_value),0)) gift_consume,
		SUM(IF(pearl_type=6,ABS(pearl_value),0)) smash_eggs_consume,
		SUM(IF(pearl_type=7,ABS(pearl_value),0)) activity_consume,
		SUM(IF(pearl_type=8,ABS(pearl_value),0)) pearl_consume,
		SUM(IF(pearl_type=10,pearl_value,0)) system_consume,
		FROM_UNIXTIME(change_timestamp,'%Y-%m-%d') created_at
	FROM
		zb_finance_pearl_stream
	WHERE
		pearl_type IN(0,6,7,8,10)
	GROUP BY
		created_at
) g_d ON t.created_at=g_d.created_at
LEFT JOIN (
	SELECT
		FROM_UNIXTIME(mall_shoplog.create_timestamp,'%Y-%m-%d') created_at,
		SUM(IF(mall_shoplog.goods_type=1, mall_shoplog.price,0)) headwear_pearl,
		SUM(IF(mall_shoplog.goods_type=2, mall_shoplog.price,0)) car_pearl,
		SUM(IF(mall_shoplog.goods_type=3, mall_shoplog.price,0)) bubble_pearl
	FROM (
		SELECT
			m_g.goods_type,
			m_s.price,
			m_s.create_timestamp
		FROM
			zb_mall_shoplog m_s
		LEFT JOIN
			zb_mall_goods m_g
		ON m_s.goods_id=m_s.goods_id
	) mall_shoplog
	GROUP BY
		created_at
) o_m_g ON o_m_g.created_at=t.created_at";

        $grand_pearl = 0; // 累计消耗
        $insert_data = [];
        foreach (DB::connection('api')->cursor($sql) as $row) {
            $grand_pearl += $row->today_pearl;
            if(empty($this->prev)){
                $growth_rate = 0;
            } else {
                $prev = array_shift($this->prev);
                if($prev['today_pearl'] > 0){
                    $growth_rate = $row->today_pearl / $prev['today_pearl'] - 1;
                } else {
                    $growth_rate = 0;
                }
            }
            array_push($insert_data, collect($row)->merge([ 'grand_pearl' => $grand_pearl, 'growth_rate' => $growth_rate ])->toArray());

            array_unshift($this->prev, [ 'today_pearl' => $row->today_pearl ]);

        }
        if($this->model->batchInsert('pearl_consume_statistics', $insert_data)){
            $this->info('ok');
        } else {
            $this->info('fail');
        }
    }



}
